package com.example.demo.dao;

/**
 * 数据库操作接口实现类，根据要求对数据库的各种增删改查操作
 */
import com.example.demo.bean.Student;
import com.example.demo.util.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.Collections;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    private QueryRunner queryRunner;

    public StudentDaoImpl() {
        this.queryRunner = new QueryRunner(DruidUtil.getDataSource());
    }

//    从数据库查询总数
    @Override
    public Long getStudentCount() {
        String sql = "select count(*) from student";
        Long query = null;
        try {
            query = queryRunner.query(sql, new ScalarHandler<>());
        } catch (SQLException troubles) {
            troubles.printStackTrace();
        }
        return query;
    }
    //    从数据库中先将数据排序再获取指定页数的信息
    @Override
    public List<Student> getPageStudent(int start,int line) {
        String sql = "select * from student order by ABS(sid) limit ?,?";
        Object[] param = {start,line};
        List<Student> query = null;
        try {
            query = queryRunner.query(sql, new BeanListHandler<Student>(Student.class),param);
        } catch (SQLException troubles) {
            troubles.printStackTrace();
        }
        return query;
    }
    //    添加信息到数据库
    @Override
    public Long addStudent(Student student) {
        String sql = "insert into student values(null,?,?,?,?,?,?)";
        Object[] param = {student.getSid(),student.getSname(),student.getGender(),
                student.getBirthday(),student.getEmail(),student.getImp()};
        Long res = null;
        try {
            res = queryRunner.insert(sql,new ScalarHandler<>(),param);
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
        return res;
    }
    //    根据给定的Student对象更新数据库中信息
    @Override
    public void setStudent(Student student) {
        String sql = "update student set sname=?,gender=?,birthday=?,email=?,imp=? where sid=?";
        Object[] param = {student.getSname(),student.getGender(),
                student.getBirthday(),student.getEmail(),student.getImp(),student.getSid()};
        try {
            queryRunner.update(sql,param);
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
    }
    //    从数据库中删除给定学号的信息
    @Override
    public void delStudent(String[] sids) {
        for (String sid: sids) {
            String sql = "delete from student where sid=?";
            try {
                queryRunner.update(sql,sid);
            } catch (SQLException troubles) {
                troubles.printStackTrace();
            }
        }
    }
    //    从数据库中根据给定的学号或者姓名查询信息
    @Override
    public List<Student> queStudent(String sid, String sname) {
        String sql1 = "select * from student where sid=?";
        String sql2 = "select * from student where sname=?";
        String sql = null;
        Object param = null;
        if (sid != "") {
            sql = sql1;
            param = sid;
        }else {
            sql = sql2;
            param = sname;
        }
        System.out.println(sql + " " + param);
        List<Student> students = null;
        try {
            students = queryRunner.query(sql,new BeanListHandler<Student>(Student.class),param);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return students;
    }
}
